package com.kcs.dc.service.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;

import com.kcs.common.utils.Constant;
import com.kcs.common.utils.LogWriter;
import com.kcs.common.utils.PropsUtils;
import com.kcs.common.utils.commonUtils;
import com.kcs.dc.service.bean.response.wsInquiryResBody;
import com.kcs.dc.service.utils.SqlCreated;

public class wsDealCenterDAO {
	
	
	/**
	 * 
	 * @param cif_id : For WSInquire (If dealId not inputted.)
	 * @param ccy : For WSInquire (If dealId not inputted.)
	 * @param buy_sell : For WSInquire (If dealId not inputted.)
	 * @param value_date : For WSInquire (If dealId not inputted.)
	 * @param dealList : All dealId / query
	 * @param conn
	 * @return Model : wsRetAdModel
	 * @throws Exception
	 */
	public wsRetAdModel[] getDataRetAD(String cif_id, String ccy,
			String buy_sell, String value_date, ArrayList<String> dealList,
			Connection conn)
			throws Exception {

		PreparedStatement ppst = null;
		ResultSet rs = null;
		ArrayList<wsRetAdModel> arrList = new ArrayList<wsRetAdModel>();
		wsRetAdModel model = null;
		wsRetAdModel[] models = null;
		
		StringBuffer sql = new StringBuffer();
		String schemaRET = PropsUtils.getMessage(Constant.PROP_DATABASE, "db.ora.schema.ret") ;

		try {
			
			// Query get data from RETAD.
			sql.append("SELECT NVL(US.telephone,' ') cif_number, Result.* ");
	    	sql.append("FROM ");
	    	sql.append("(SELECT D.gid_id deal_id ");
	    	sql.append("   	,NVL(D.parent#parent,' ') parent_id ");
	    	sql.append("   	,Case when D.fx_cross_deal#type# = 'CANCELLATION' then R.maker_account ");
	    	sql.append("			else D.taker_name End cust_tax_id ");
	    	sql.append("   	,Case when D.fx_cross_deal#type# = 'CANCELLATION' then trim(R.account_long_name) ");
	    	sql.append(" 			else trim(D.taker_id) End cust_name ");
	    	sql.append("   	,L.type# trans_type ");
	    	sql.append("   	,Case when L.taker_buys_base# = 'Y' then 'B' ");
	    	sql.append(" 	 		else 'S' End buy_sell ");
	    	sql.append("   	,D.deal_date trans_date ");
	    	sql.append("   	,L.value_date#value_date value_date");
	    	sql.append("   	,Case when D.fx_cross_deal#ccy1 in ('THS','THP','THG','THZ') then 'THB' "); 
	    	sql.append("   		else D.fx_cross_deal#ccy1 End ccy1 ");
	    	sql.append("		,Case when L.dealt_ccy <> D.fx_cross_deal#ccy1 then L.contra_amount ");
	    	sql.append("			else L.dealt_amount End deal_amt1 ");
	    	sql.append("		,L.quote#all_in exch_rate");
	    	sql.append("		,Case when D.fx_cross_deal#ccy2 in ('THS','THP','THG','THZ') then 'THB'");
	    	sql.append("			else D.fx_cross_deal#ccy2 End ccy2"); 
	    	sql.append("		,Case when L.DEALT_CCY <> D.fx_cross_deal#ccy1 then L.dealt_amount");
	    	sql.append("			else L.contra_amount End equi_amt");
	    	sql.append("		,Case when L.DEALT_CCY <> D.fx_cross_deal#ccy1 then L.contra_amount_outstanding");
	    	sql.append("			else L.amount_outstanding End outstd_amt");
	    	sql.append("		,NVL(substr(D.taker_custom,instr(D.taker_custom,'=',1,1)+1,instr(D.taker_custom,',',1,1)-instr(D.taker_custom,'=',1,1)-1),' ') instrument");
	    	sql.append("		,NVL(substr(D.taker_custom,instr(D.taker_custom,'=',1,3)+1,instr(D.taker_custom,',',1,3)-instr(D.taker_custom,'=',1,3)-1),' ') branch");
	    	sql.append("		,NVL(substr(D.taker_custom,instr(D.taker_custom,'=',1,4)+1,instr(D.taker_custom,',',1,4)-instr(D.taker_custom,'=',1,4)-1),' ') comments");
	    	sql.append("		,Case when D.fx_cross_deal#type# = 'CANCELLATION'  or R.cancelled_by_name is not null THEN 'CANCEL'");
	    	sql.append("		 else 'NEW' End doc_type ");
	    	sql.append("		,' ' ibc_code");
	    	sql.append("		,D.maker_id");
	    	sql.append("		,D.maker_full_name  makerfullname");
	    	sql.append("		,D.proxy_group_full_name  proxygroupfullname");
	    	sql.append("		,D.proxy_full_name  proxyfullname");                            
	    	sql.append("		,D.last_modified#last_modified last_modify");
	    	sql.append("	FROM " + schemaRET +".deal D, " + schemaRET + ".leg L, " + schemaRET + ".requirement R");
	    	sql.append("	WHERE D.status = 'Completed' ");
	    	sql.append("		  and D.id = L.owner");
	    	sql.append("		  and (D.fx_cross_deal#type# = L.type# or substr(L.type#,1,4) = 'SWAP')");
	    	sql.append("		  and L.id = R.owner(+)");
	    	sql.append("		  and (D.fx_cross_deal#ccy1 in ('THS','THP','THG','THZ','THB') or D.fx_cross_deal#ccy2 in ('THS','THP','THG','THZ','THB'))");
	    	
	    	if (dealList.size() > 0){
				// Specific dealId
				String whereDealIdInParam = SqlCreated.createWhereClauseInParam(dealList.size());
				sql.append("		  and ((D.gid_id in (");	
		    	sql.append(whereDealIdInParam);	
		    	sql.append("						  )) or (D.parent#parent in (");
		    	sql.append(whereDealIdInParam);
		    	sql.append(")))");
			}else{
				// Specific cifNo, ccy, buy_sell
				sql.append("		  and L.value_date#value_date = ?");	//--PARAMETER No.1
			}	
	    	
	    	sql.append(" ) Result, " + schemaRET + ".users US ");
	    	sql.append(" WHERE Result.cust_tax_id = Us.username");
	    	
	    	if (dealList.size() == 0){
	    		// Specific cifNo, ccy, buy_sell
	    		sql.append(" and Us.telephone = ?"); 						//--PARAMETER No.2
		    	sql.append(" and Result.ccy1 = ?");	          				//--PARAMETER No.3
		    	sql.append(" and Result.buy_sell = ?");        				//--PARAMETER No.4
	    	}
				
	    	//LogWriter.debug("SQL getDataRetAd :" + sql.toString(), Constant.LOGGER_WEBSERVICE);
	    	LogWriter.debug("SQL getDataRetAd", Constant.LOGGER_WEBSERVICE);
			ppst = conn.prepareStatement(sql.toString());
			
			if (dealList.size() > 0){
				// Specific dealId
				SqlCreated.setWhereClaseDoubleIn(ppst, dealList);
			}else{
				// Specific cifNo, ccy, buy_sell
				ppst.setString(1, value_date);
				ppst.setString(2, cif_id);
				ppst.setString(3, ccy);
				ppst.setString(4, buy_sell);
			}

			// get data
			rs = ppst.executeQuery();
			while (rs.next()) {
				model = new wsRetAdModel();
		
				model.setDealID(rs.getString("deal_id"));
				model.setParentID(rs.getString("parent_id"));
				model.setCifNumber(rs.getString("cif_number"));
				model.setCustName(rs.getString("cust_name"));
				model.setTransType(rs.getString("trans_type"));
				model.setBuySell(rs.getString("buy_sell"));
				model.setTransDate(rs.getString("trans_date"));
				model.setValueDate(rs.getString("value_date"));
				model.setCcy1(rs.getString("ccy1"));
				model.setDealAmt(rs.getDouble("deal_amt1"));
				model.setExchRate(rs.getDouble("exch_rate"));
				model.setCcy2(rs.getString("ccy2"));
				model.setEquiAmt(rs.getDouble("equi_amt"));
				model.setOutstdAmt(rs.getDouble("outstd_amt"));
				model.setInstrument(rs.getString("instrument"));
				model.setBranch(rs.getString("branch"));
				model.setComments(rs.getString("comments"));
				model.setDocType(rs.getString("doc_type"));				
				model.setIbcCode(rs.getString("ibc_code"));
				model.setMakerId(rs.getString("maker_id"));
				model.setMaker_full_name(rs.getString("makerfullname"));
				model.setProxy_group_full_name(rs.getString("proxygroupfullname"));
				model.setProxy_full_name(rs.getString("proxyfullname"));
				model.setLast_mod_date(rs.getString("last_modify"));

				arrList.add(model);
			}

			if (arrList.size() > 0) {
				models = (wsRetAdModel[]) arrList.toArray(new wsRetAdModel[arrList.size()]);
				return models;
			}
		} catch (Exception e) {
			LogWriter.error("1.1 wsDealCenterDAO [Method : getDataRetAD] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
			throw new Exception();
		} finally {
			if (ppst != null) {
				try {
					ppst.close();
				} catch (SQLException e) {
					LogWriter.error("1.2 wsDealCenterDAO [Method : getDataRetAD] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
					throw new Exception();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					LogWriter.error("1.3 wsDealCenterDAO [Method : getDataRetAD] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
					throw new Exception();
				}
			}			
		}
		return null;
	}
	
	public String getDocTypeDC(String deal_id, Connection conn) throws Exception {
		
		PreparedStatement ppst = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		String doctype = "";
		
		try {
			sql.append("SELECT DOCUMENT_TYPE FROM DEALS WHERE DEAL_ID = ? ");
			
			ppst = conn.prepareStatement(sql.toString());
			ppst.setString(1, deal_id);
			
			rs = ppst.executeQuery();
			while (rs.next()) {
				doctype = rs.getString(1);
			}
				
		} catch (Exception e) {
			LogWriter.error("1.1 wsDealCenterDAO [Method : getDocTypeDC] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
			throw new Exception();
		} finally {
			if (ppst != null) {
				try {
					ppst.close();
				} catch (SQLException e) {
					LogWriter.error("1.2 wsDealCenterDAO [Method : getDocTypeDC] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
					throw new Exception();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					LogWriter.error("1.3 wsDealCenterDAO [Method : getDocTypeDC] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
					throw new Exception();
				}
			}
		}
		
		return doctype;
	}
	
	public boolean insertDealCenter(wsRetAdModel RetadModel, Connection dcConn, String mod_user) throws Exception {
		
		int num_record = 0;
		CallableStatement proc = null;

		try {
			proc = dcConn.prepareCall("{ call insert_deals_proc(?, ?, ?, ?, ?, ?, ?, ?, ?, ?," +
															  " ?, ?, ?, ?, ?, ?, ?, ?, ?, ?," +
															  " ?, ?, ?, ?, ?, ?) }");
				
			proc.setString(1, RetadModel.getDealID());
			proc.setString(2, RetadModel.getParentID());
			proc.setString(3, RetadModel.getCifNumber());
			proc.setString(4, RetadModel.getCustName());
			proc.setString(5, RetadModel.getTransType());
			proc.setString(6, RetadModel.getBuySell());
			proc.setString(7, RetadModel.getTransDate()); 
			proc.setString(8, RetadModel.getValueDate()); 
			proc.setString(9, RetadModel.getCcy1());
			proc.setDouble(10, RetadModel.getDealAmt());
			proc.setDouble(11, RetadModel.getExchRate());
			proc.setString(12, RetadModel.getCcy2());
			proc.setDouble(13, RetadModel.getEquiAmt());
			proc.setDouble(14, RetadModel.getOutstdAmt());
			proc.setString(15, RetadModel.getInstrument());
			proc.setString(16, RetadModel.getBranch());
			proc.setString(17, RetadModel.getComments());
			proc.setString(18, RetadModel.getMakerId());			
			proc.setString(19, RetadModel.getMaker_full_name());
			proc.setString(20, RetadModel.getProxy_group_full_name());
			proc.setString(21, RetadModel.getProxy_full_name());			
			proc.setString(22, RetadModel.getIbcCode());
			proc.setString(23, RetadModel.getDocType());
			proc.setString(24, RetadModel.getLast_mod_date());			
			proc.setString(25, mod_user);
			proc.registerOutParameter(26, Types.INTEGER);
				
			proc.execute();  			
			dcConn.commit();
			
			num_record = proc.getInt(26);  
			LogWriter.debug("Deal ID :" + RetadModel.getDealID() + ", Return effected row : " + num_record, Constant.LOGGER_WEBSERVICE);	
				
			if(num_record >= 0)
				return true;
			
		} catch (Exception e) {
			LogWriter.error("1.1 wsDealCenterDAO [Method : insertDealCenter call Store insert_deals_proc] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
			throw new Exception();
		} finally {
			if (proc != null) {
				try {
					proc.close();
				} catch (SQLException e) {
					LogWriter.error("1.2 wsDealCenterDAO [Method : insertDealCenter call Store insert_deals_proc] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
					throw new Exception();
				}
			}
		}
		return false;  //store return -1 : occur exception.
	}	
		
	public boolean updateDealsCenter(String deal_id, double outstd, String doc_type,
			Connection dcConn, String mod_user, String cif_num, String comment) throws Exception {
		
		int num_record = 0;
		CallableStatement proc = null;
		
		try {
			proc = dcConn.prepareCall("{ call update_deals_fromret_proc(?, ?, ?, ?, ?, ?, ?) }");
			proc.setString(1, deal_id);
			proc.setDouble(2, outstd);
			proc.setString(3, doc_type);
			proc.setString(4, cif_num);
			proc.setString(5, comment);
			proc.setString(6, mod_user);
			proc.registerOutParameter(7, Types.INTEGER);
			
			proc.execute();   
			dcConn.commit();
			
			num_record = proc.getInt(7);			
			LogWriter.debug("Deal ID :" + deal_id + ", Return update success : " + num_record, Constant.LOGGER_WEBSERVICE);
			
			if(num_record == 0)
				return true;
			
		} catch (Exception e) {
			LogWriter.error("1.1 wsDealCenterDAO [Method : updateDealsDocType call Store update_deals_fromret_proc] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
			throw new Exception();
		} finally {
			if (proc != null) {
				try {
					proc.close();
				} catch (SQLException e) {
					LogWriter.error("1.2 wsDealCenterDAO [Method : updateDealsDocType call Store update_deals_fromret_proc] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
					throw new Exception();
				}
			}		
		}
		return false;  //store return -1 : occur exception.
	}
	
	public wsInquiryResBody[] getDealsFromInquire(String deal_id, String cif_id,
			String ccy, String buy_sell, String value_date, Connection conn)
			throws Exception {

		CallableStatement proc = null;
		ResultSet rs = null;
		ArrayList<wsInquiryResBody> arrList = new ArrayList<wsInquiryResBody>();
		wsInquiryResBody model = null;
		wsInquiryResBody[] models = null;
		String lastdatetime = "";
		

		try {
			
			proc = conn.prepareCall("{ call qry_deals_proc(?, ?, ?, ?, ?, ?) }");

			proc.setString(1, deal_id);
			proc.setString(2, cif_id);
			proc.setString(3, ccy);
			proc.setString(4, buy_sell);
			proc.setString(5, value_date);
			proc.registerOutParameter(6, Types.INTEGER);

			// get data
			rs = proc.executeQuery();
			while (rs.next()) {
				model = new wsInquiryResBody();
		
				model.setDealId(rs.getString(1));
				model.setParentId(rs.getString(2));
				model.setCifNumber(rs.getString(3));
				model.setCustName(rs.getString(4));
				model.setTransType(rs.getString(5));
				model.setBuySell(rs.getString(6));
				model.setDealDate(commonUtils.convertFormatDate(rs.getString(7), commonUtils.yyyymmdd, commonUtils.ddmmyyyywithslash));
				model.setValueDate(commonUtils.convertFormatDate(rs.getString(8), commonUtils.yyyymmdd, commonUtils.ddmmyyyywithslash));
				model.setCcy1(rs.getString(9));			
				model.setDealAmt1(Double.parseDouble(commonUtils.formatNumber(rs.getDouble(10), 2, "0",false)));
				model.setExchRate(Double.valueOf(commonUtils.formatNumber(rs.getDouble(11), 8, "#",false)));
				model.setCcy2(rs.getString(12));
				model.setEquiAmt(Double.valueOf(commonUtils.formatNumber(rs.getDouble(13), 2, "0",false)));
				model.setOutsdAmt(Double.valueOf(commonUtils.formatNumber(rs.getDouble(14), 2, "0",false)));				
				model.setInstrumentType(rs.getString(15));
				model.setBranch(rs.getString(16));
				model.setComments(rs.getString(17));
				model.setMakerId(rs.getString(18));				
				model.setIbcCode(rs.getString(19));
				model.setDocumentType(rs.getString(20));
				
				lastdatetime = commonUtils.formatDate(rs.getTimestamp(21), commonUtils.ddmmyyyyhhmmsswithslash);
				model.setLastModifyDate(lastdatetime);
				arrList.add(model);
			}	

			if (arrList.size() > 0) {
				models = (wsInquiryResBody[]) arrList.toArray(new wsInquiryResBody[arrList.size()]);
				
			}else{
				models = new wsInquiryResBody[0];
			}
			
			return models;
			
		} catch (Exception e) {
			LogWriter.error("1.1 wsDealCenterDAO [Method : getDealsFromInquire] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
			throw new Exception();
		} finally {
			if (proc != null) {
				try {
					proc.close();
				} catch (SQLException e) {
					LogWriter.error("1.2 wsDealCenterDAO [Method : getDealsFromInquire] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
					throw new Exception();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					LogWriter.error("1.3 wsDealCenterDAO [Method : getDealsFromInquire] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
					throw new Exception();
				}
			}			
		}
	}	
	
	/**
	 * Only WebService (WS002B or Settlement Past(screen)) ==> System = DCTP
	 * @param deal_id
	 * @param value_date
	 * @param conn
	 * @return
	 * @throws Exception
	 */
	public wsSettleModel getDealsSettlePast(String deal_id, String value_date, Connection conn) throws Exception {

		PreparedStatement ppst = null;
		wsSettleModel model = null;
		
		StringBuffer sql_past = new StringBuffer();
		ResultSet rs_past = null;
		
		StringBuffer sql_today = new StringBuffer();
		ResultSet rs_today = null;

		try {

			sql_past.append("SELECT deal_amt1, dbo.cal_deals_outstanding(deal_id, ?) as outstd_amt FROM MAT_DEALS ");
			sql_past.append("WHERE deal_id = ? and document_type = 'NEW' ");
			
			sql_today.append("SELECT deal_amt1, dbo.cal_deals_outstanding(deal_id, value_date) as outstd_amt FROM DEALS ");
			sql_today.append("WHERE deal_id = ? and value_date = ? and document_type = 'NEW' ");

			ppst = conn.prepareStatement(sql_past.toString());
			ppst.setString(1, value_date);
			ppst.setString(2, deal_id);

			// get data (past)
			rs_past = ppst.executeQuery();		
			
			while (rs_past.next()) {
				model = new wsSettleModel();

				model.setDealAmt(rs_past.getDouble("deal_amt1"));
				model.setOutsdAmt(rs_past.getDouble("outstd_amt"));
	
			}
			
			
			// get data (today)
			if (model == null){
				ppst = conn.prepareStatement(sql_today.toString());
				ppst.setString(1, deal_id);
				ppst.setString(2, value_date);
				
				// get data (today)
				rs_today = ppst.executeQuery();	
				while (rs_today.next()) {
					model = new wsSettleModel();
	
					model.setDealAmt(rs_today.getDouble("deal_amt1"));
					model.setOutsdAmt(rs_today.getDouble("outstd_amt"));
	
				}
			}

		} catch (Exception e) {
			LogWriter.error("1.1 wsDealCenterDAO [Method : getDealsPastSettle] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
			throw new Exception();
		} finally {
			if (ppst != null) {
				try {
					ppst.close();
				} catch (SQLException e) {
					LogWriter.error("1.2 wsDealCenterDAO [Method : getDealsPastSettle] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
					throw new Exception();
				}
			}
			if (rs_past != null) {
				try {
					rs_past.close();
				} catch (SQLException e) {
					LogWriter.error("1.3 wsDealCenterDAO [Method : getDealsPastSettle] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
					throw new Exception();
				}
			}
			if (rs_today != null) {
				try {
					rs_today.close();
				} catch (SQLException e) {
					LogWriter.error("1.4 wsDealCenterDAO [Method : getDealsPastSettle] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
					throw new Exception();
				}
			}
		}
		return model;
	}
	
	public wsSettleModel getDealsFromSettle(String deal_id, String value_date,
			Connection conn) throws Exception {

		PreparedStatement ppst = null;
		ResultSet rs = null;
		wsSettleModel model = null;

		StringBuffer sql = new StringBuffer();

		try {

			sql.append("SELECT deal_amt1, dbo.cal_deals_outstanding(deal_id, value_date) as outstd_amt FROM DEALS ");
			sql.append("WHERE deal_id = ? and value_date = ? and document_type = 'NEW' ");

			ppst = conn.prepareStatement(sql.toString());
			ppst.setString(1, deal_id);
			ppst.setString(2, value_date);
//			ppst.setString(3, deal_id);
//			ppst.setString(4, value_date);

			// get data
			rs = ppst.executeQuery();
			while (rs.next()) {
				model = new wsSettleModel();

				model.setDealAmt(rs.getDouble("deal_amt1"));
				model.setOutsdAmt(rs.getDouble("outstd_amt"));

			}

		} catch (Exception e) {
			LogWriter.error("1.1 wsDealCenterDAO [Method : getDealsFromSettle] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
			throw new Exception();
		} finally {
			if (ppst != null) {
				try {
					ppst.close();
				} catch (SQLException e) {
					LogWriter.error("1.2 wsDealCenterDAO [Method : getDealsFromSettle] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
					throw new Exception();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					LogWriter.error("1.3 wsDealCenterDAO [Method : getDealsFromSettle] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
					throw new Exception();
				}
			}
		}
		return model;
	}	
	
	/*public wsSettleModel getDealsSettleTodayAndPast(String deal_id, String value_date, Connection conn) throws Exception {

		PreparedStatement ppst = null;
		ResultSet rs = null;
		wsSettleModel model = null;

		StringBuffer sql = new StringBuffer();

		try {

			// Past
			sql.append("SELECT deal_amt1, dbo.cal_deals_outstanding(deal_id, ?) as outstd_amt FROM MAT_DEALS ");
			sql.append("WHERE deal_id = ? and document_type = 'NEW' ");
			sql.append("union ");
			// Today
			sql.append("SELECT deal_amt1, dbo.cal_deals_outstanding(deal_id, value_date) as outstd_amt FROM DEALS ");
			sql.append("WHERE deal_id = ? and value_date = ? and document_type = 'NEW' ");

			ppst = conn.prepareStatement(sql.toString());
			ppst.setString(1, value_date);
			ppst.setString(2, deal_id);

			// get data
			rs = ppst.executeQuery();
			while (rs.next()) {
				model = new wsSettleModel();

				model.setDealAmt(rs.getDouble("deal_amt1"));
				model.setOutsdAmt(rs.getDouble("outstd_amt"));

			}

		} catch (Exception e) {
			LogWriter.error("1.1 wsDealCenterDAO [Method : getDealsPastSettle] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
			throw new Exception();
		} finally {
			if (ppst != null) {
				try {
					ppst.close();
				} catch (SQLException e) {
					LogWriter.error("1.2 wsDealCenterDAO [Method : getDealsPastSettle] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
					throw new Exception();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					LogWriter.error("1.3 wsDealCenterDAO [Method : getDealsPastSettle] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
					throw new Exception();
				}
			}
		}
		return model;
	}*/
	
	public boolean insertDealMovement(Connection dcConn, String deal_id,
			String settleDate, double settle_amt, String mod_user,
			String requestId, String src_ref, String service_id) throws Exception {
		
		int num_record = 0;
		PreparedStatement ppst = null;
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO dbo.DEALS_MOVEMENT ");
		sql.append("(deal_id, settle_date, settle_amt, last_mod_user, last_mod_date, ws_request_id, ws_src_ref, ws_service_id)");
		sql.append("VALUES (?,?,?,?,getDate(),?,?,?)");
		
		try {
				ppst = dcConn.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
				
				ppst.setString(1, deal_id); 
				ppst.setString(2, settleDate);
				ppst.setDouble(3, settle_amt);
				ppst.setString(4, mod_user);			
				ppst.setString(5, requestId);
				ppst.setString(6, src_ref);	
				ppst.setString(7, service_id);
					
				num_record = ppst.executeUpdate();
				
				if(num_record > 0)
					return true;
			
		} catch (Exception e) {
			LogWriter.error("1.1 wsDealCenterDAO [Method : insertDealMovement] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
			throw new Exception();
		} finally {
			if (ppst != null) {
				try {
					ppst.close();
				} catch (SQLException e) {
					LogWriter.error("1.2 wsDealCenterDAO [Method : insertDealMovement] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
					throw new Exception();
				}
			}
		}
		return false;
	}	
	
public String getDealValueDatePast(Connection conn, String deal_id) throws Exception {
		
		PreparedStatement ppst = null;
		ResultSet rs = null;
		StringBuffer sql = new StringBuffer();
		String valuedate = "";
		
		try {
			sql.append("SELECT VALUE_DATE FROM MAT_DEALS WHERE DEAL_ID = ? ");
			
			ppst = conn.prepareStatement(sql.toString());
			ppst.setString(1, deal_id);
			
			rs = ppst.executeQuery();
			while (rs.next()) {
				valuedate = rs.getString(1);
			}
				
		} catch (Exception e) {
			LogWriter.error("1.1 wsDealCenterDAO [Method : getDealValueDatePast] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
			throw new Exception();
		} finally {
			if (ppst != null) {
				try {
					ppst.close();
				} catch (SQLException e) {
					LogWriter.error("1.2 wsDealCenterDAO [Method : getDealValueDatePast] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
					throw new Exception();
				}
			}
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					LogWriter.error("1.3 wsDealCenterDAO [Method : getDealValueDatePast] : " + e.getMessage(), Constant.LOGGER_WEBSERVICE);
					throw new Exception();
				}
			}
		}
		
		return valuedate;
	}
	
}
